from common import PlayEntity,SearchFilter
import DatabaseConnection

class PlayData(object):
    def __init__(self):
        pass

    def InsertPlay(self,play):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsPlay',(play.DJ,play.SongID,play.DateTime))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdatePlay(self,play):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdPlay',(play.DJ,play.SongID,play.DateTime))
        cursor.close()
        conn.commit()
        conn.close()

    def DeletePlay(self,djID,sID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelPlay',(djID,sID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type PlayEntity or None (if there is no record with this ID)
    def SelectPlayByID(self,ID):
        query = """ SELECT DJ,SongID,DateTime,DJName,SongName
                    FROM vwPlay
                    WHERE DJ='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return PlayEntity.PlayEntity(DJ=result[0][0],songID=result[0][1],dateTime=result[0][2],DJName=result[0][3],songName=result[0][4])
        else:
            return None

    def SelectPlayBySongID(self,sID):
        query = """ SELECT DJ,SongID,DateTime,DJName,SongName
                    FROM vwPlay
                    WHERE SongID='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return PlayEntity.PlayEntity(DJ=result[0][0],songID=result[0][1],dateTime=result[0][2],DJName=result[0][3],songName=result[0][4])
        else:
            return None


    #This method returns a list of objects of type PlayEntity
    def SelectPlayByFilter(self,searchFilter):
        query = """ SELECT DJ,SongID,DateTime
                    FROM vwPlay"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(PlayEntity.PlayEntity(DJ=record[0],songID=record[1],dateTime=record[2],DJName=record[3],songName=record[4]))
        return recordSet

    def SelectAllPlays(self):
        query = """ SELECT DJ,SongID,DateTime
                    FROM vwPlay"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(PlayEntity.PlayEntity(DJ=record[0],songID=record[1],dateTime=record[2],DJName=record[3],songName=record[4]))
        return recordSet

    def SelectLastPlay(self):
        query = """ SELECT DJ,SongID,DateTime,DJName,SongName
                    FROM vwPlay
                    WHERE DateTime=(SELECT MAX(DateTime) FROM Play) """

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return PlayEntity.PlayEntity(DJ=result[0][0],songID=result[0][1],dateTime=result[0][2],DJName=result[0][3],songName=result[0][4])
        else:
            return None

